﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using VerticaAPI.model;
using System.Data;

namespace VerticaAPI
{
    /// <summary>
    /// 尺码管理
    /// </summary>
    [Description("尺码管理:核心码列表、批量修改陈列量")]
    public class SizeTypeHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        [MustLogin]
        [Description("核心码列表")]
        [ReturnType(typeof(IList<SizeTypeModel.AllParamModel>))]
        public ApiResult GetSizeType([ParamDescription("类别")]string KindID, [ParamDescription("性别")]string SexName = "",
            [ParamDescription("关键字")]string Keys = "",
            [ParamDescription("商品区域")]string ShopArea = "",
            [ParamDescription("省")]string Province = "",
            [ParamDescription("市")]string City = "",
            [ParamDescription("版型")]string ClothesVersion = "")
        {
            //使用like判断，直接使用%
            if (string.IsNullOrEmpty(SexName)) { SexName = "%"; }
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlErpSize = $@"
                            SELECT  si.SizeName ,
                                    0 AS SizeCoreFlag
                            FROM    nods.nods_erp_SD_Mat_Size si
                            WHERE   si.AllowUsed = 1
                                    AND ExtrSource = '{UserInfo.UserExtrSource}' AND nvl(Remark3,'') <>  '电商品牌使用'
                            ORDER BY sizeCodeType ,
                                    to_number(SizeCodeSort) ";
                var sqlSize =
                 //优化后SQL 将* 去除，只取必要的数据。 GavinGu 20171206
                 $@"SELECT  eba.AreaName ,
                          eba.AreaID ,
                          st.SizeName ,
                          st.DisplayQty ,
                          eba.FullName ,
                          st.SizeCoreFlag
                  FROM    nods.nods_erp_Bas_Area eba
                          LEFT JOIN ( SELECT  CompanyID,AreaID,ExtrSource,ParentAreaID
                                      FROM    ndw.ndw_AreaProCity
                                      WHERE   ExtrSource = '{UserInfo.UserExtrSource}' AND CompanyID = '{UserInfo.UserCompanyId}'
                                      ) apc ON eba.AreaID = apc.AreaID AND eba.ExtrSource = apc.ExtrSource
                          LEFT JOIN ( SELECT  SizeName,DisplayQty,SizeCoreFlag,SizeID,AreaID,ExtrSource,CompanyID
                                      FROM    ndw.ndw_SizeType
                                      WHERE   ExtrSource = '{UserInfo.UserExtrSource}' AND CompanyID = '{UserInfo.UserCompanyId}'
                                              AND KindID = '{KindID}' AND SexName like '%{SexName}'
                        {(string.IsNullOrWhiteSpace(ClothesVersion) ? $" AND (ClothesVersionCode='{ClothesVersion}' OR ClothesVersionCode IS NULL) " : $" AND ClothesVersionCode='{ClothesVersion}' ")}
                                             
                                      ) st ON st.AreaID = eba.AreaID AND eba.ExtrSource = st.ExtrSource
                          LEFT JOIN ( SELECT   Remark3,SizeID,ExtrSource
                                      FROM    nods.nods_erp_SD_Mat_Size
                                      WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                              AND nvl(Remark3,'') <>  '电商品牌使用'
                                      ) esms ON st.SizeID = esms.SizeID AND eba.ExtrSource = esms.ExtrSource
                   WHERE   eba.Level = 2  AND eba.ExtrSource = '{UserInfo.UserExtrSource}' 
                  {(string.IsNullOrWhiteSpace(Keys) ? "" : $" and eba.AreaName like '%{Keys}%' ")}
                  {(string.IsNullOrWhiteSpace(ShopArea) ? "" : $" and apc.ParentAreaID = '{ShopArea}' ")}
                  {(string.IsNullOrWhiteSpace(Province) ? "" : $" and eba.ParentID = '{Province}' ")}
                  {(string.IsNullOrWhiteSpace(City) ? "" : $@" and eba.AreaID ='{City}'")}
                  order by eba.AreaID";
                #region 旧SQL 可用(未优化)
                /*
                                $@"SELECT  eba.AreaName ,
                                           eba.AreaID ,
                                           st.SizeName ,
                                           st.DisplayQty ,
                                           eba.FullName ,
                                           st.SizeCoreFlag
                                   FROM    nods.nods_erp_Bas_Area eba
                                           LEFT JOIN ( SELECT  *
                                                       FROM    ndw.ndw_AreaProCity
                                                       WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                               AND CompanyID = '{UserInfo.UserCompanyId}'
                                                       ) apc ON eba.AreaID = apc.AreaID
                                                               AND eba.ExtrSource = apc.ExtrSource
                                           LEFT JOIN ( SELECT  *
                                                       FROM    ndw.ndw_SizeType
                                                       WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                               AND CompanyID = '{UserInfo.UserCompanyId}'
                                                               AND KindID = '{KindID}'
                                                               AND SexName like '{SexName}'
                                                       ) st ON st.AreaID = eba.AreaID
                                                               AND eba.ExtrSource = st.ExtrSource
                                                               AND apc.CompanyID = st.CompanyID
                                           LEFT JOIN ( SELECT  *
                                                       FROM    nods.nods_erp_SD_Mat_Size
                                                       WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                               AND nvl(Remark3,'') <>  '电商品牌使用'
                                                       ) esms ON st.SizeID = esms.SizeID
                                                               AND eba.ExtrSource = esms.ExtrSource
                                   WHERE   eba.Level = 2
                                           AND eba.ExtrSource = '{UserInfo.UserExtrSource}' 
                                   {(string.IsNullOrWhiteSpace(Keys) ? "" : $" and eba.AreaName like '%{Keys}%' ")}
                                   {(string.IsNullOrWhiteSpace(ShopArea) ? "" : $" and apc.ParentAreaID = '{ShopArea}' ")}
                                   {(string.IsNullOrWhiteSpace(Province) ? "" : $" and eba.ParentID = '{Province}' ")}
                                   {(string.IsNullOrWhiteSpace(City) ? "" : $@" and eba.AreaID ='{City}' ")}
                                   order by eba.AreaID";
                                 */
                #endregion
                //找出所有尺码
                var cmdErpSize = Helper.GetSqlStringCommand(sqlErpSize);
                var dtErpSize = Helper.ExecuteDataTable(cmdErpSize);
                //找出所有城市的核心码
                var cmdSize = Helper.GetSqlStringCommand(sqlSize);
                var dtSize = Helper.ExecuteDataTable(cmdSize);
                var listErpSize = ModelConvertHelper<SizeTypeModel.ErpSizeModel>.ConvertToModel(dtErpSize);
                var listSize = ModelConvertHelper<SizeTypeModel.ShowModel>.ConvertToModel(dtSize).OrderBy(d => d.AreaID)
                    .GroupBy(d => d.AreaID, d => d).ToList();
                var result = new SizeTypeModel.AllParamModel
                {
                    ErpSizeList = listErpSize,
                    SizeList = listSize
                };
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [JsonType]
        [MustLogin]
        [Description("批量修改陈列量")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult BatchSizeType([ParamDescription("陈列量")]SizeTypeModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空!");
                if (param.list == null) return Error("参数对象为空!");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var index = 0;
                ModSizeType(param.list, param.list[0].SexName, out index);
                Log.WriteLog($"成功设置{index}条数据{(param.list.Count() - index == 0 ? "" : $@",有{param.list.Count() - index}条错误数据")}");
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 改变类型
        /// </summary>
        /// <param name="list"></param>
        public void ChangeType(IList<SizeTypeModel.ImportModel> list, out DataTable error_dt, out int index)
        {
            try
            {
                var ExtrSource = UserInfo.UserExtrSource;

                var cmdList = new List<CommandInfo>();
                var newList = new List<SizeTypeModel.ImportModel>();
                var errList = new List<SizeTypeModel.ImportModel>();
                var saveList = new List<SizeTypeModel.CreatImportModel>();
                var sizeDic = GetOdsErpSDMatSizeList().ToDictionary(d => d.SizeName, d => d.SizeID);
                //一次性查询所有list，不要一次次检查
                var ClothesVersionList = GetClothesVersion();
                //筛选有用数据。排除无用数据并且导出到Excel中
                foreach (var item in list)
                {

                    //陈列量为空或者小于等0直接排除
                    if (string.IsNullOrWhiteSpace(item.DisplayQty) || Convert.ToInt32(item.DisplayQty) <= 0)
                    {
                        errList.Add(new SizeTypeModel.ImportModel
                        {
                            FullName = item.FullName,
                            AreaID = item.AreaID,
                            SizeName = item.SizeName,
                            DisplayQty = "陈列量【" + item.DisplayQty + "】必须不能为空且大于零",
                            KindID = string.IsNullOrWhiteSpace(item.KindID) ? "" : item.KindID,
                            SexName = string.IsNullOrWhiteSpace(item.SexName) ? "" : item.SexName,
                            SizeCoreFlag = string.IsNullOrWhiteSpace(item.SizeCoreFlag) ? "0" : item.SizeCoreFlag,
                            ClothesVersionCode = string.IsNullOrWhiteSpace(item.ClothesVersionCode) ? "" : item.ClothesVersionCode
                        });
                        continue;
                    }

                    var nece = new SizeTypeModel.NeceModel();
                    //当版型不为空时， 检查类型是否为男装或者女装，否则提示 
                    if (!string.IsNullOrWhiteSpace(item.ClothesVersionCode) && item.KindID != "000001" && item.KindID != "000002")
                    {
                        //错误提示
                        errList.Add(new SizeTypeModel.ImportModel
                        {
                            FullName = item.FullName,
                            AreaID = item.AreaID,
                            SizeName = item.SizeName,
                            DisplayQty = string.IsNullOrWhiteSpace(item.DisplayQty) ? "" : item.DisplayQty,
                            KindID = "类别【" + item.KindID + "】当版型不为空时,只能为男装[000001]或女装[000002]",
                            SexName = string.IsNullOrWhiteSpace(item.SexName) ? "" : item.SexName,
                            SizeCoreFlag = string.IsNullOrWhiteSpace(item.SizeCoreFlag) ? "0" : item.SizeCoreFlag,
                            ClothesVersionCode = string.IsNullOrWhiteSpace(item.ClothesVersionCode) ? "" : item.ClothesVersionCode
                        });
                        continue;
                    }
                    //当版型不为空时且类型为男装或者女装时，判断 ClothesVersionCode 使用的是Code[B001]还是Name[修身]
                    if (!string.IsNullOrWhiteSpace(item.ClothesVersionCode) && (item.KindID == "000001" || item.KindID == "000002"))
                    {
                        //Code不存在数据库中
                        if (ClothesVersionList.Where(p => p.ClothesVersionCode == item.ClothesVersionCode).Count() <= 0)
                        {
                            //Name存在数据库中
                            if (ClothesVersionList.Where(p => p.ClothesVersionName == item.ClothesVersionCode).Count() > 0)
                            {
                                newList.Add(new SizeTypeModel.ImportModel
                                {
                                    // FullName =  item.FullName,
                                    AreaID = item.AreaID,
                                    SizeName = item.SizeName,
                                    DisplayQty = string.IsNullOrWhiteSpace(item.DisplayQty) ? "" : item.DisplayQty,
                                    KindID = string.IsNullOrWhiteSpace(item.KindID) ? "" : item.KindID,
                                    SexName = string.IsNullOrWhiteSpace(item.SexName) ? "" : item.SexName,
                                    SizeCoreFlag = string.IsNullOrWhiteSpace(item.SizeCoreFlag) ? "0" : item.SizeCoreFlag,
                                    ClothesVersionCode = ClothesVersionList.FirstOrDefault(p => p.ClothesVersionName == item.ClothesVersionCode).ClothesVersionCode
                                });
                                continue;
                            }
                            //Code&&Nmae都不存在数据库中
                            errList.Add(new SizeTypeModel.ImportModel
                            {
                                FullName = item.FullName,
                                AreaID = item.AreaID,
                                SizeName = item.SizeName,
                                DisplayQty = string.IsNullOrWhiteSpace(item.DisplayQty) ? "" : item.DisplayQty,
                                KindID = string.IsNullOrWhiteSpace(item.KindID) ? "" : item.KindID,
                                SexName = string.IsNullOrWhiteSpace(item.SexName) ? "" : item.SexName,
                                SizeCoreFlag = string.IsNullOrWhiteSpace(item.SizeCoreFlag) ? "0" : item.SizeCoreFlag,
                                ClothesVersionCode = "版型【" + item.ClothesVersionCode + "】不正确 !"
                            });
                            continue;
                        }
                        continue;
                    }

                    newList.Add(new SizeTypeModel.ImportModel
                    {
                        //FullName = item.FullName,
                        AreaID = item.AreaID,
                        SizeName = item.SizeName,
                        DisplayQty = string.IsNullOrWhiteSpace(item.DisplayQty) ? "" : item.DisplayQty,
                        KindID = string.IsNullOrWhiteSpace(item.KindID) ? "" : item.KindID,
                        SexName = string.IsNullOrWhiteSpace(item.SexName) ? "" : item.SexName,
                        SizeCoreFlag = string.IsNullOrWhiteSpace(item.SizeCoreFlag) ? "0" : item.SizeCoreFlag,
                        ClothesVersionCode = string.IsNullOrWhiteSpace(item.ClothesVersionCode) ? "" : item.ClothesVersionCode,
                    });


                }
                var sqlDel = $@" DELETE  FROM ndw.ndw_SizeType
                                    WHERE   AreaID = '{{0}}'
                                            AND KindID = '{{1}}'
                                            AND SexName = '{{2}}'
                                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}' ";
                #region  需要保存到数据库的数据处理
                foreach (var i in newList)
                {
                    var saveModel = new SizeTypeModel.CreatImportModel
                    {
                        AreaID = i.AreaID,
                        CompanyID = UserInfo.UserCompanyId,
                        ExtrSource = UserInfo.UserExtrSource,
                        DisplayQty = i.DisplayQty,
                        SizeCoreFlag = i.SizeCoreFlag,
                        SizeName = i.SizeName.Trim(' '),
                        SizeID = sizeDic[i.SizeName.Trim(' ')],
                        KindID = i.KindID,
                        SexName = i.SexName,
                        ClothesVersionCode = i.ClothesVersionCode
                    };
                    //删除要新增的数据
                    var cmdDel = new CommandInfo()
                    {
                        CommandText =
                        string.Format(sqlDel + (string.IsNullOrWhiteSpace(i.ClothesVersionCode) ? $" AND (ClothesVersionCode='' OR ClothesVersionCode IS NULL)" : $" AND ClothesVersionCode='{i.ClothesVersionCode}'")
                        , i.AreaID, i.KindID, i.SexName, i.ClothesVersionCode),
                    };
                    cmdList.Add(cmdDel);
                    switch (i.SizeName.Trim(' '))
                    {
                        case "XXL":
                            saveModel.SizeName2 = "2XL";
                            saveModel.SizeTypeFlag = "字母码";
                            break;
                        case "XL":
                        case "3XL":
                        case "4XL":
                        case "JM":
                        case "L":
                        case "M":
                        case "S":
                        case "F":
                            saveModel.SizeTypeFlag = "字母码";
                            break;
                        default:
                            saveModel.SizeTypeFlag = "数字码";
                            break;
                    }
                    saveList.Add(saveModel);
                }
                #endregion
                var dt = DataTableHelper.LinqToDataTable(saveList);
                int cc = Helper.ExecuteSqlTran(cmdList, dt, "ndw.ndw_SizeType");
                index = list.Count - errList.Count;
                error_dt = DataTableHelper.LinqToDataTable(errList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #region  改版，此版本搭配 城市核心码Old.xls 使用
        /*
        public void ChangeType(IList<SizeTypeModel.SizeModel> list, out DataTable error_dt, out int index)
        {
            try
            {
                var newList = new List<SizeTypeModel.ListModel>();
                foreach (var item in list)
                {
                    var n = new SizeTypeModel.ListModel();
                    n.CityID = item.AreaID;
                    n.KindID = item.KindID;
                    n.SexName = item.SexName;
                    n.FullName = item.FullName;
                    n.ClothesVersionCode = item.ClothesVersionCode;
                    n.Children = new List<SizeTypeModel.NeceModel>();
                    var properties =
                        item.GetType()
                            .GetProperties(System.Reflection.BindingFlags.Instance |
                                           System.Reflection.BindingFlags.Public);
                    foreach (System.Reflection.PropertyInfo itp in properties)
                    {
                        if (item.KindID == "000001" || item.KindID == "000002")
                        {
                            var name = itp.Name;
                            var m = new SizeTypeModel.NeceModel();
                            m.KindID = item.KindID;
                            m.SexName = item.SexName;
                            m.AreaID = item.AreaID;
                            m.ClothesVersionCode = item.ClothesVersionCode;
                            #region switch

                            switch (name)
                            {
                                case "F":
                                    if (item.F == 0) break;
                                    m.SizeName = "F";
                                    m.DisplayQty = item.F;
                                    n.Children.Add(m);
                                    break;
                                case "S":
                                    if (item.S == 0) break;
                                    m.SizeName = "S";
                                    m.DisplayQty = item.S;
                                    n.Children.Add(m);
                                    break;
                                case "XS":
                                    if (item.S == 0) break;
                                    m.SizeName = "XS";
                                    m.DisplayQty = item.XS;
                                    n.Children.Add(m);
                                    break;
                                case "XXS":
                                    if (item.S == 0) break;
                                    m.SizeName = "XXS";
                                    m.DisplayQty = item.XXS;
                                    n.Children.Add(m);
                                    break;
                                case "M":
                                    if (item.M == 0) break;
                                    m.SizeName = "M";
                                    m.DisplayQty = item.M;
                                    n.Children.Add(m);
                                    break;
                                case "L":
                                    if (item.L == 0) break;
                                    m.SizeName = "L";
                                    m.DisplayQty = item.L;
                                    n.Children.Add(m);
                                    break;
                                case "XL":
                                    if (item.XL == 0) break;
                                    m.SizeName = "XL";
                                    m.DisplayQty = item.XL;
                                    n.Children.Add(m);
                                    break;
                                case "XXL":
                                    if (item.XXL == 0) break;
                                    m.SizeName = "XXL";
                                    m.DisplayQty = item.XXL;
                                    n.Children.Add(m);
                                    break;
                                case "N3XL":
                                    if (item.N3XL == 0) break;
                                    m.SizeName = "3XL";
                                    m.DisplayQty = item.N3XL;
                                    n.Children.Add(m);
                                    break;
                                case "N4XL":
                                    if (item.N4XL == 0) break;
                                    m.SizeName = "4XL";
                                    m.DisplayQty = item.N4XL;
                                    n.Children.Add(m);
                                    break;
                                case "JM":
                                    if (item.JM == 0) break;
                                    m.SizeName = "JM";
                                    m.DisplayQty = item.JM;
                                    n.Children.Add(m);
                                    break;
                                case "N27":
                                    if (item.N27 == 0) break;
                                    m.SizeName = "27";
                                    m.DisplayQty = item.N27;
                                    n.Children.Add(m);
                                    break;
                                case "N28":
                                    if (item.N28 == 0) break;
                                    m.SizeName = "28";
                                    m.DisplayQty = item.N28;
                                    n.Children.Add(m);
                                    break;
                                case "N29":
                                    if (item.N29 == 0) break;
                                    m.SizeName = "29";
                                    m.DisplayQty = item.N29;
                                    n.Children.Add(m);
                                    break;
                                case "N30":
                                    if (item.N30 == 0) break;
                                    m.SizeName = "30";
                                    m.DisplayQty = item.N30;
                                    n.Children.Add(m);
                                    break;
                                case "N31":
                                    if (item.N31 == 0) break;
                                    m.SizeName = "31";
                                    m.DisplayQty = item.N31;
                                    n.Children.Add(m);
                                    break;
                                case "N32":
                                    if (item.N32 == 0) break;
                                    m.SizeName = "32";
                                    m.DisplayQty = item.N32;
                                    n.Children.Add(m);
                                    break;
                                case "N33":
                                    if (item.N33 == 0) break;
                                    m.SizeName = "33";
                                    m.DisplayQty = item.N33;
                                    n.Children.Add(m);
                                    break;
                                case "N34":
                                    if (item.N34 == 0) break;
                                    m.SizeName = "34";
                                    m.DisplayQty = item.N34;
                                    n.Children.Add(m);
                                    break;
                                case "N35":
                                    if (item.N35 == 0) break;
                                    m.SizeName = "35";
                                    m.DisplayQty = item.N35;
                                    n.Children.Add(m);
                                    break;
                                case "N36":
                                    if (item.N36 == 0) break;
                                    m.SizeName = "36";
                                    m.DisplayQty = item.N36;
                                    n.Children.Add(m);
                                    break;
                                case "N37":
                                    if (item.N37 == 0) break;
                                    m.SizeName = "37";
                                    m.DisplayQty = item.N37;
                                    n.Children.Add(m);
                                    break;
                                case "N38":
                                    if (item.N38 == 0) break;
                                    m.SizeName = "38";
                                    m.DisplayQty = item.N38;
                                    n.Children.Add(m);
                                    break;
                                case "N39":
                                    if (item.N39 == 0) break;
                                    m.SizeName = "39";
                                    m.DisplayQty = item.N39;
                                    n.Children.Add(m);
                                    break;
                                case "N40":
                                    if (item.N40 == 0) break;
                                    m.SizeName = "40";
                                    m.DisplayQty = item.N40;
                                    n.Children.Add(m);
                                    break;
                                case "N41":
                                    if (item.N41 == 0) break;
                                    m.SizeName = "41";
                                    m.DisplayQty = item.N41;
                                    n.Children.Add(m);
                                    break;
                                case "N42":
                                    if (item.N42 == 0) break;
                                    m.SizeName = "42";
                                    m.DisplayQty = item.N42;
                                    n.Children.Add(m);
                                    break;
                                case "N43":
                                    if (item.N43 == 0) break;
                                    m.SizeName = "43";
                                    m.DisplayQty = item.N43;
                                    n.Children.Add(m);
                                    break;
                                case "N44":
                                    if (item.N44 == 0) break;
                                    m.SizeName = "44";
                                    m.DisplayQty = item.N44;
                                    n.Children.Add(m);
                                    break;
                                case "N45":
                                    if (item.N45 == 0) break;
                                    m.SizeName = "45";
                                    m.DisplayQty = item.N45;
                                    n.Children.Add(m);
                                    break;
                                default:
                                    break;
                            }

                            #endregion
                        }
                    }
                    newList.Add(n);
                }
                ModSizeType(newList, out error_dt, out index);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        */
        #endregion



        /// <summary>
        /// 批量修改陈列量
        /// </summary>
        /// <param name="list"></param>
        public void ModSizeType(IList<SizeTypeModel.ListModel> list, string SexName, out int index)
        {


            try
            {
                var sqlDel = $@" DELETE  FROM ndw.ndw_SizeType
                                    WHERE   AreaID = '{{0}}'
                                            AND KindID = '{{1}}'
                                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}'
                                            AND SexName like '%{SexName}'
                ";
                var cmdList = new List<CommandInfo>();
                var newList = new List<SizeTypeModel.CreatModel>();
                int ErrorCount = 0;
                var errList = new List<SizeTypeModel.ErrorModel>();
                var sizeDic = GetOdsErpSDMatSizeList().ToDictionary(d => d.SizeName, d => d.SizeID);
                //一次性查询所有list，不要一次次检查
                var ClothesVersionList = GetClothesVersion();
                foreach (var item in list)
                {

                    var cmdDel = new CommandInfo()
                    {
                        CommandText =
                            string.Format(sqlDel, item.CityID, item.KindID, item.SexName, item.ClothesVersionCode),
                    };
                    cmdList.Add(cmdDel);

                    //当版型不为空时， 检查类型是否为男装或者女装。
                    if (!string.IsNullOrWhiteSpace(item.ClothesVersionCode) && item.KindID != "000001" && item.KindID != "000002")
                    {
                        ErrorCount++;
                        continue;
                    }

                    //ClothesVersionCode不为空且 Code不存在
                    if (!string.IsNullOrWhiteSpace(item.ClothesVersionCode) && (item.KindID == "000001" || item.KindID == "000002") && ClothesVersionList.Where(p => p.ClothesVersionCode == item.ClothesVersionCode).Count() <= 0)
                    {
                        ErrorCount++;
                        continue;
                    }

                    foreach (var i in item.Children)
                    {
                        if (!sizeDic.Keys.Contains(i.SizeName.Trim(' '))) continue;

                        var creatModel = new SizeTypeModel.CreatModel
                        {
                            AreaID = item.CityID,
                            CompanyID = UserInfo.UserCompanyId,
                            ExtrSource = UserInfo.UserExtrSource,
                            DisplayQty = i.DisplayQty,
                            SizeCoreFlag = i.SizeCoreFlag,
                            SizeName = i.SizeName.Trim(' '),
                            SizeID = sizeDic[i.SizeName.Trim(' ')],
                            KindID = i.KindID,
                            SexName = i.SexName,
                            ClothesVersionCode = i.ClothesVersionCode
                        };
                        switch (i.SizeName.Trim(' '))
                        {
                            case "XXL":
                                creatModel.SizeName2 = "2XL";
                                creatModel.SizeTypeFlag = "字母码";
                                break;
                            case "XL":
                            case "3XL":
                            case "4XL":
                            case "JM":
                            case "L":
                            case "M":
                            case "S":
                            case "F":
                                creatModel.SizeTypeFlag = "字母码";
                                break;
                            default:
                                creatModel.SizeTypeFlag = "数字码";
                                break;
                        }
                        newList.Add(creatModel);
                    }
                }
                var dt = DataTableHelper.LinqToDataTable(newList);
                int cc = Helper.ExecuteSqlTran(cmdList, dt, "ndw.ndw_SizeType");
                index = list.Count - ErrorCount;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        /// <summary>
        /// 获取尺码列表
        /// </summary>
        /// <returns></returns>
        public IList<OdsErpSDMatSize.ModifyModel> GetOdsErpSDMatSizeList()
        {
            try
            {
                var sql = $@"SELECT  SizeID ,
        SizeName ,
        SizeCodeSort
FROM    nods.nods_erp_SD_Mat_Size
WHERE   AllowUsed = 1
        AND ExtrSource = '{UserInfo.UserExtrSource}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<OdsErpSDMatSize.ModifyModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        [MustLogin]
        [Description("获取城市核心码数量(首页图表专用)")]
        [ReturnType(typeof(IList<SizeTypeModel.ChartModel>))]
        public ApiResult GetSizetypeCount()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@" SELECT  SizeName ,
        ExtrSource ,
        CompanyID ,
        SUM(DisplayQty) AS DisplayQtySum
FROM    ndw.ndw_sizetype
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY SizeName ,
        ExtrSource ,
        CompanyID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<SizeTypeModel.ChartModel>.ConvertToModel(dt).OrderBy(d => d.SizeName);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        /// <summary>
        /// 获取尺码类型列表
        /// </summary>
        /// <returns></returns>
        [MustLogin]
        [Description("获取尺码类型列表")]
        [ReturnType(typeof(IList<OdsErpSDMatSize.ListModel>))]
        public ApiResult GetSizeCodeTypeList()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT DISTINCT
        SizeCodeType
FROM    nods.nods_erp_SD_Mat_Size
WHERE   AllowUsed = 1
        AND SizeCodeType IS NOT NULL
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<OdsErpSDMatSize.ListModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        /// <summary>
        /// 根据尺码类型获取对应的尺码列表
        /// </summary>
        /// <returns></returns>
        [MustLogin]
        [Description("根据尺码类型获取对应的尺码列表")]
        [ReturnType(typeof(IList<OdsErpSDMatSize.ModifyModel>))]
        public ApiResult GetSizeList([ParamDescription("尺码类型")]string sizeCodeType)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT SizeID ,
                                    SizeName
                            FROM    nods.nods_erp_SD_Mat_Size
                            WHERE   AllowUsed = 1
                                    AND ExtrSource = '{UserInfo.UserExtrSource}'
                                    AND SizeCodeType IS NOT NULL
                                    AND SizeCodeType = '{sizeCodeType}'
                            ORDER BY SizeCodeSort ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<OdsErpSDMatSize.ModifyModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-06-05
        [MustLogin]
        [Description("返回品类尺码信息")]
        [ReturnType(typeof(IList<OdsErpSDMatSize.ListModel>))]
        public ApiResult ReturnModelSize()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT  a.ModelID ,
                                    b.SizeID ,
                                    b.SizeName ,
                                    b.SizeCodeType
                            FROM    ( SELECT  ModelID, SizeTypeFlag, ExtrSource
                                      FROM      ngoods.ngoods_PL_Bas_ModelSizeType
                                      WHERE     CompanyID = '{UserInfo.UserCompanyId}'
                                                AND ExtrSource = '{UserInfo.UserExtrSource}'
                                    ) a ,
                                    (SELECT SizeID, SizeName, SizeCodeType, AllowUsed, ExtrSource, sizeCodeSort
                                      FROM      nods.nods_erp_SD_Mat_Size
                                      WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                    ) b
                            WHERE   a.SizeTypeFlag = b.SizeCodeType
                                    AND a.ExtrSource = b.ExtrSource
                                    AND b.AllowUsed = 1
                            ORDER BY sizeCodeType ,
                                    sizeCodeSort";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var query = ModelConvertHelper<OdsErpSDMatSize.ListModel>.ConvertToModel(dt).GroupBy(d => d.ModelID);
                var result = query.Select(d => new OdsErpSDMatSize.ShowModel
                {
                    list = d.GroupBy(r => r.ModelID + "_" + r.SizeCodeType).ToList()
                });
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        [MustLogin]
        [Description("返回性别")]
        [ReturnType(typeof(IList<SizeTypeModel.GenderModel>))]
        public ApiResult GetGenderList()
        {
            //GenderList
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@" SELECT SexName FROM ndw.ndw_SizeType WHERE SexName IS NOT NULL AND ExtrSource='{UserInfo.UserExtrSource}' GROUP BY SexName";

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<SizeTypeModel.GenderModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

        }


        [MustLogin]
        [Description("返回类别")]
        [ReturnType(typeof(IList<SizeTypeModel.KindModel>))]
        public ApiResult GetKindList()
        {
            //GenderList
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@" SELECT KindID,KindName FROM nods.nods_erp_sd_mat_kind WHERE ExtrSource='{UserInfo.UserExtrSource}' AND AllowUsed=1  ORDER BY KindID ASC";

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<SizeTypeModel.KindModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

        }

        [Description("查询版型数据")]
        //[ReturnType(typeof(IList<SizeTypeModel.ClothesVersionModel>))]
        public IList<SizeTypeModel.ClothesVersionModel> GetClothesVersion()
        {
            var sql = $@" SELECT distinct ClothesVersionName,ClothesVersionCode From nods.nods_erp_SD_Mat_ClothesVersion 
            WHERE ExtrSource='{UserInfo.UserExtrSource}' AND AllowUsed=1  ORDER BY ClothesVersionCode ASC";

            var cmd = Helper.GetSqlStringCommand(sql);
            var dt = Helper.ExecuteDataTable(cmd);
            IList<SizeTypeModel.ClothesVersionModel> list = ModelConvertHelper<SizeTypeModel.ClothesVersionModel>.ConvertToModel(dt);
            return list;
        }



        [MustLogin]
        [Description("返回版型数据")]
        [ReturnType(typeof(IList<SizeTypeModel.ClothesVersionModel>))]
        public ApiResult GetClothesVersionList()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                return Success(GetClothesVersion());
            }
            catch (Exception ex)
            {
                throw (ex);
            }

        }
    }
}
